﻿using Common;
using SqliteDataAccess.SqliteDataManagerTableAdapters;
using SqliteDataAccess;
using System;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Globalization;
using System.Diagnostics;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Drawing.Printing;

namespace Business
{
    public class BukuKasUmumBusiness : BaseBusiness
    {
        BukuKasUmumTableAdapter bukuKasUmumTA;
        public BukuKasUmumBusiness()
        {
            bukuKasUmumTA = new BukuKasUmumTableAdapter();
        }

        public override int Load()
        {
            throw new NotImplementedException();
        }

        public int LoadByBulanTahun(String namaBulan, int tahun)
        {
            int bulan = (int)Enums.GetMonthByName(namaBulan);
            int ret = bukuKasUmumTA.Fill(LppbopSqlite.BukuKasUmum, bulan, tahun);
            return ret;
        }

        public override int Save()
        {
            try
            {
                int affected = bukuKasUmumTA.Update(LppbopSqlite.BukuKasUmum);
                if (LppbopSqlite.BukuKasUmum.Rows.Count > 0)
                {
                    SqliteDataManager.BukuKasUmumRow first = LppbopSqlite.BukuKasUmum.Rows[0] as SqliteDataManager.BukuKasUmumRow;
                    bukuKasUmumTA.Fill(LppbopSqlite.BukuKasUmum, (long)first["bulan"], (long)first["tahun"]);
                }
                return affected;

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public void Add(IDictionary<string, object> fieldValues)
        {
            bool withPajak = Convert.ToBoolean(fieldValues["pajak"]);
            long sortOrder = 0;

            SqliteDataManager.BukuKasUmumRow row = LppbopSqlite.BukuKasUmum.NewBukuKasUmumRow();
            SetRowValues(ref row, fieldValues);
            if (row.sort_order == 0) // add to last
            {
                int count = LppbopSqlite.BukuKasUmum.Rows.Count;
                if (count == 0) row.sort_order = 1; // first record
                else row.sort_order = Convert.ToInt32(LppbopSqlite.BukuKasUmum[count - 1]["sort_order"]) + 1;
            }
            else // insert in the middle
            {
                IncrementSortOrder(row.sort_order, withPajak);
            }
            sortOrder = row.sort_order;
            LppbopSqlite.BukuKasUmum.AddBukuKasUmumRow(row);

            Save();
            //NOTE: row object is stale here, should no longer use it

            long lastInsertedId = GetLastInsertedId();
            if (withPajak)
            {
                IList<SqliteDataManager.BukuKasUmumRow> pajakRows = AddPajakRows(fieldValues, 0, sortOrder);
                LppbopSqlite.BukuKasUmum.AddBukuKasUmumRow(pajakRows[0]);
                LppbopSqlite.BukuKasUmum.AddBukuKasUmumRow(pajakRows[1]);
                Save();
            }

        }

        public void Delete(IList<long> ids)
        {
            foreach (long id in ids)
            {
                foreach (SqliteDataManager.BukuKasUmumRow child in GetChildRows(id))
                {
                    child.Delete();
                }
                SqliteDataManager.BukuKasUmumRow row = LppbopSqlite.BukuKasUmum.FindByid(id);
                row.Delete();
            }
            Save();
        }

        public void Edit(IDictionary<string, object> fieldValues)
        {
            long id = Convert.ToInt64(fieldValues["id"]);
            long sortOrder = Convert.ToInt64(fieldValues["sort_order"]);
            SqliteDataManager.BukuKasUmumRow row = LppbopSqlite.BukuKasUmum.FindByid(id);
            SetRowValues(ref row, fieldValues);
            Save();

            IList<SqliteDataManager.BukuKasUmumRow> childRows = GetChildRows(id);
            bool withPajak = Convert.ToBoolean(fieldValues["pajak"]);

            if (withPajak && childRows.Count == 0) // insert new pajak
            {
                IList<SqliteDataManager.BukuKasUmumRow> pajakRows = AddPajakRows(fieldValues, id, sortOrder);
                LppbopSqlite.BukuKasUmum.AddBukuKasUmumRow(pajakRows[0]);
                LppbopSqlite.BukuKasUmum.AddBukuKasUmumRow(pajakRows[1]);
                IncrementSortOrder(sortOrder + 1, withPajak);
            }
            else if (!withPajak && childRows.Count > 0) // delete pajak
            {
                foreach (var child in childRows) child.Delete();
            }
            else if (withPajak && childRows.Count > 0) // update pajak
            {
                long besarPajak = Convert.ToInt64(fieldValues["pajak_yang_disetor"]);
                foreach (var child in childRows)
                {
                    if (!child.IspenerimaanNull()) child.penerimaan = besarPajak;
                    else child.pengeluaran = besarPajak;
                }
            }
            Save();
        }

        private IList<SqliteDataManager.BukuKasUmumRow> AddPajakRows(IDictionary<string, object> fieldValues, long parentId, long sortOrder)
        {
            if (parentId == 0) parentId = GetLastInsertedId();

            SqliteDataManager.BukuKasUmumRow pajakYangDisetor = NewPajakRow(fieldValues);
            SqliteDataManager.BukuKasUmumRow pajakYangDiterima = NewPajakRow(fieldValues);

            pajakYangDiterima.penerimaan = Convert.ToInt64(fieldValues["pajak_yang_disetor"]);
            pajakYangDiterima.uraian += " yang diterima";
            pajakYangDiterima.parent_id = parentId;
            pajakYangDiterima.sort_order = sortOrder + 1;

            pajakYangDisetor.pengeluaran = Convert.ToInt64(fieldValues["pajak_yang_disetor"]);
            pajakYangDisetor.uraian += " yang disetor";
            pajakYangDisetor.parent_id = parentId;
            pajakYangDisetor.sort_order = sortOrder + 2;

            var list = new List<SqliteDataManager.BukuKasUmumRow>();
            list.Add(pajakYangDisetor);
            list.Add(pajakYangDiterima);
            return list;
        }

        private SqliteDataManager.BukuKasUmumRow NewPajakRow(IDictionary<string, object> fieldValues)
        {
            SqliteDataManager.BukuKasUmumRow pajakRow = LppbopSqlite.BukuKasUmum.NewBukuKasUmumRow();
            pajakRow.bulan = (int)Enums.GetMonthByName(fieldValues["bulan"].ToString());
            pajakRow.tahun = Convert.ToInt32(fieldValues["tahun"]);
            pajakRow.uraian = fieldValues["jenis_pajak"].ToString();
            pajakRow.kode = fieldValues["kode_pajak"].ToString();
            pajakRow.sort_order = Convert.ToInt32(fieldValues["sort_order"]);
            return pajakRow;
        }

        private void IncrementSortOrder(long from, bool withPajak)
        {
            int incrementOrder = 1;
            if (withPajak) incrementOrder = 3;

            foreach (SqliteDataManager.BukuKasUmumRow r in LppbopSqlite.BukuKasUmum.Rows)
            {
                if (r.sort_order >= from)
                    r.sort_order += incrementOrder;
            }
        }

        private void SetRowValues(ref SqliteDataManager.BukuKasUmumRow row, IDictionary<string, object> fieldValues)
        {
            long id = Convert.ToInt64(fieldValues["id"]);
            if (id > 0)
                row.id = id;
            row.bulan = (int)Enums.GetMonthByName(fieldValues["bulan"].ToString());
            row.tahun = Convert.ToInt32(fieldValues["tahun"]);
            row.no = fieldValues["nomor"].ToString();
            row.tanggal = (DateTime)fieldValues["tanggal"];
            row.kode = fieldValues["pos_rekening"].ToString();
            row.uraian = fieldValues["uraian"].ToString();
            row.penerimaan = Convert.ToInt64(fieldValues["penerimaan"]);
            row.pengeluaran = Convert.ToInt64(fieldValues["pengeluaran"]);
            row.sort_order = Convert.ToInt32(fieldValues["sort_order"]);
        }

        public SqliteDataManager.UraianPenggunaanDataTable GetUraianPenggunaan()
        {
            UraianPenggunaanTableAdapter ta = new UraianPenggunaanTableAdapter();
            return ta.GetData();
        }

        private long GetLastInsertedId()
        {
            DataView data = LppbopSqlite.BukuKasUmum.DefaultView;
            data.Sort = "id desc";
            DataTable table = data.ToTable();
            return (long)table.Rows[0]["id"];
        }

        private IList<SqliteDataManager.BukuKasUmumRow> GetChildRows(long id)
        {
            IList<SqliteDataManager.BukuKasUmumRow> childRows = new List<SqliteDataManager.BukuKasUmumRow>();
            foreach (SqliteDataManager.BukuKasUmumRow child in LppbopSqlite.BukuKasUmum.Rows)
            {
                if (!child.Isparent_idNull() && child.parent_id == id)
                    childRows.Add(child);
            }
            return childRows;
        }

        public void ExportToExcel(string filename, SqliteDataManager.BukuKasUmumDataTable bukuKasUmum)
        {
            WriteToExcel(filename, bukuKasUmum);
        }

        public void PrintExcel(SqliteDataManager.BukuKasUmumDataTable bukuKasUmum, PrinterSettings printerSettings)
        {
            string fileName = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";
            WriteToExcel(fileName, bukuKasUmum);

            //RawPrinterHelper.SendFileToPrinter(printerSettings.PrinterName, fileName);
            //RawPrinterHelper.SendStringToPrinter(printerSettings.PrinterName, "Buku Kas Umum");
            PrintUtil.SendToPrinter(fileName, printerSettings);
            File.Delete(fileName);

            //try
            //{
            //    ProcessStartInfo processInfo = new ProcessStartInfo();
            //    processInfo.FileName = fileName;
            //    processInfo.WindowStyle = ProcessWindowStyle.Hidden;
            //    processInfo.Verb = "Print";
            //    processInfo.CreateNoWindow = true;
            //    processInfo.UseShellExecute = true;
            //    Process process = Process.Start(processInfo);
            //    if (process != null)
            //        process.WaitForExit();
            //}
            //catch (Exception)
            //{
            //    throw;
            //}
            //finally
            //{
            //    File.Delete(fileName);
            //}
        }

        public byte[] AsByteArray(SqliteDataManager.BukuKasUmumDataTable bukuKasUmum)
        {
            if (bukuKasUmum.Count == 0) return null;

            SqliteDataManager.ProfilSekolahRow profil = new ProfilBusiness().GetProfil();

            using (ExcelPackage package = new ExcelPackage(new FileInfo(String.Format(@"templates\{0}", "buku-kas-umum.xlsx"))))
            {
                ExcelWorksheet ws = package.Workbook.Worksheets[1];

                int start;
                int row = start = 8;
                foreach (SqliteDataManager.BukuKasUmumRow bku in bukuKasUmum.Rows)
                {
                    ws.InsertRow(row, 1);
                    ws.Cells["A" + row].Value = bku.IsnoNull() ? "" : bku.no;
                    if (bku.IstanggalNull())
                        ws.Cells["B" + row].Value = "";
                    else ws.Cells["B" + row].Value = bku.tanggal;
                    ws.Cells["C" + row].Value = bku.IsuraianNull() ? "" : bku.uraian;
                    ws.Cells["D" + row].Value = bku.IspenerimaanNull() ? 0 : bku.penerimaan;
                    ws.Cells["E" + row].Value = bku.IspengeluaranNull() ? 0 : bku.pengeluaran;

                    SetBorderAll(ws.Cells[String.Format("A{0}:E{0}", row)]);
                    row++;
                }

                // jumlah total, pajak, sisa dana
                ws.InsertRow(row, 4);
                int bulan = (int)bukuKasUmum[0].bulan;
                int tahun = (int)bukuKasUmum[0].tahun;
                decimal danaAwal = GetTotalPenerimaan(bulan, tahun), totalJumlah = GetTotalPengeluaran(bulan, tahun), totalPajak = GetTotalPajak(bulan, tahun);

                // preset cells value
                string[] cols = { "A", "B", "C", "D", "E" };
                for (int i = row; i < row + 4; i++)
                {
                    for (int j = 0; j < cols.Length; j++)
                    {
                        ws.Cells[cols[j] + i].Value = "";
                    }
                }

                ws.Cells["C" + row].Value = "Jumlah total";
                ws.Cells["E" + row++].Value = totalJumlah;

                ws.Cells["C" + row].Value = "Total pajak yang disetor";
                ws.Cells["E" + row++].Value = totalPajak;

                ws.Cells["C" + row].Value = "Total Pengeluaran";
                ws.Cells["E" + row++].Value = totalJumlah - totalPajak;

                ws.Cells["C" + row].Value = "Sisa dana";
                ws.Cells["E" + row++].Value = danaAwal - (totalJumlah - totalPajak);

                SetBorderAll(ws.Cells[String.Format("A{0}:E{1}", (row - 4), row)]);

                // nama sekolah
                ws.Cells["A2"].Value = profil.sekolah;
                // bulan tahun
                ws.Cells["C3"].Value = string.Format(": {0} {1}", Enums.GetMonthByName(bukuKasUmum[0].bulan.ToString()), bukuKasUmum[0].tahun);
                // format tanggal
                ws.Cells[string.Format("B{0}:B{1}", start, row - 1)].Style.Numberformat.Format = "dd/MM/YYYY";
                // format uraian wrapped
                ws.Cells[string.Format("C{0}:C{1}", start, row - 1)].Style.WrapText = true;
                // format penerimaan/pengeluaran
                ws.Cells[string.Format("D{0}:E{1}", start, row - 1)].Style.Numberformat.Format = "_(Rp* #,##0_);_(Rp* (#,##0);_(Rp* \" - \"??_);_(@_)";
                // tempat, tanggal
                ws.Cells[string.Format("D{0}", row + 1)].Value = string.Format("Jakarta, {0}", DateTime.Now.ToString("dd MMMM yyyy"));
                // ketua yayasan
                ws.Cells[string.Format("A{0}", row + 8)].Value = profil.ketua_yayasan;
                // kepala sekolah
                ws.Cells[string.Format("C{0}", row + 8)].Value = profil.kepala_sekolah;
                // bendahara sekolah
                ws.Cells[string.Format("D{0}", row + 8)].Value = profil.bendahara_sekolah;

                return package.GetAsByteArray();
            }
        }

        private void WriteToExcel(string filename, SqliteDataManager.BukuKasUmumDataTable bukuKasUmum)
        {
            if (bukuKasUmum.Count == 0 || string.IsNullOrEmpty(filename)) return;

            SqliteDataManager.ProfilSekolahRow profil = new ProfilBusiness().GetProfil();

            using (ExcelPackage package = new ExcelPackage(new FileInfo(String.Format(@"templates\{0}", "buku-kas-umum.xlsx"))))
            {
                ExcelWorksheet ws = package.Workbook.Worksheets[1];

                int start;
                int row = start = 8;
                foreach (SqliteDataManager.BukuKasUmumRow bku in bukuKasUmum.Rows)
                {
                    ws.InsertRow(row, 1);
                    ws.Cells["A" + row].Value = bku.IsnoNull() ? "" : bku.no;
                    if (bku.IstanggalNull())
                        ws.Cells["B" + row].Value = "";
                    else ws.Cells["B" + row].Value = bku.tanggal;
                    ws.Cells["C" + row].Value = bku.IsuraianNull() ? "" : bku.uraian;
                    ws.Cells["D" + row].Value = bku.IspenerimaanNull() ? 0 : bku.penerimaan;
                    ws.Cells["E" + row].Value = bku.IspengeluaranNull() ? 0 : bku.pengeluaran;

                    SetBorderAll(ws.Cells[String.Format("A{0}:E{0}", row)]);
                    row++;
                }

                // jumlah total, pajak, sisa dana
                ws.InsertRow(row, 4);
                int bulan = (int)bukuKasUmum[0].bulan;
                int tahun = (int)bukuKasUmum[0].tahun;
                decimal danaAwal = GetTotalPenerimaan(bulan, tahun), totalJumlah = GetTotalPengeluaran(bulan, tahun), totalPajak = GetTotalPajak(bulan, tahun);

                // preset cells value
                string[] cols = { "A", "B", "C", "D", "E"};
                for (int i = row; i < row + 4; i++)
                {
                    for (int j = 0; j < cols.Length; j++)
                    {
                        ws.Cells[cols[j] + i].Value = "";
                    }    
                }

                ws.Cells["C" + row].Value = "Jumlah total";
                ws.Cells["E" + row++].Value = totalJumlah;
                
                ws.Cells["C" + row].Value = "Total pajak yang disetor";
                ws.Cells["E" + row++].Value = totalPajak;
                
                ws.Cells["C" + row].Value = "Total Pengeluaran";
                ws.Cells["E" + row++].Value = totalJumlah - totalPajak;
                
                ws.Cells["C" + row].Value = "Sisa dana";
                ws.Cells["E" + row++].Value = danaAwal - (totalJumlah - totalPajak);

                SetBorderAll(ws.Cells[String.Format("A{0}:E{1}", (row - 4), row)]);

                // nama sekolah
                ws.Cells["A2"].Value = profil.sekolah;
                // bulan tahun
                ws.Cells["C3"].Value = string.Format(": {0} {1}", Enums.GetMonthByName(bukuKasUmum[0].bulan.ToString()), bukuKasUmum[0].tahun);
                // format tanggal
                ws.Cells[string.Format("B{0}:B{1}", start, row - 1)].Style.Numberformat.Format = "dd/MM/YYYY";
                // format uraian wrapped
                ws.Cells[string.Format("C{0}:C{1}", start, row - 1)].Style.WrapText = true;
                // format penerimaan/pengeluaran
                ws.Cells[string.Format("D{0}:E{1}", start, row - 1)].Style.Numberformat.Format = "_(Rp* #,##0_);_(Rp* (#,##0);_(Rp* \" - \"??_);_(@_)";
                // tempat, tanggal
                ws.Cells[string.Format("D{0}", row + 1)].Value = string.Format("Jakarta, {0}", DateTime.Now.ToString("dd MMMM yyyy"));
                // ketua yayasan
                ws.Cells[string.Format("A{0}", row + 8)].Value = profil.ketua_yayasan;
                // kepala sekolah
                ws.Cells[string.Format("C{0}", row + 8)].Value = profil.kepala_sekolah;
                // bendahara sekolah
                ws.Cells[string.Format("D{0}", row + 8)].Value = profil.bendahara_sekolah;

                if (!filename.EndsWith("xlsx")) filename += ".xlsx";
                package.SaveAs(new FileInfo(filename));
            }
        }

        private void SetBorderAll(ExcelRange cells)
        {
            foreach (ExcelRangeBase cell in cells)
            {
                cell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                cell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                cell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                cell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            }

        }

        public decimal GetTotalPenerimaan(int bulan, int tahun)
        {
            object value = bukuKasUmumTA.TotalPenerimaan(bulan, tahun);
            return value == null ? 0 : Convert.ToDecimal(value);
        }

        public decimal GetTotalPengeluaran(int bulan, int tahun)
        {
            object value = bukuKasUmumTA.TotalPengeluaran(bulan, tahun);
            return value == null ? 0 : Convert.ToDecimal(value);
        }

        public decimal GetTotalPajak(int bulan, int tahun)
        {
            object value = bukuKasUmumTA.TotalPajakDisetor(bulan, tahun);
            return value == null ? 0 : Convert.ToDecimal(value);
        }


    }
}
